using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Metadata.Edm;
using System.Data.Common;
using System.Data.Objects;
using System.Data.Linq;
using System.Data.EntityClient;
using System.Diagnostics;
using System.Linq;
using SampleSupport;
using System.Xml.Linq;
using System.Text;
using System.IO;
using NorthwindEFModel;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace QuerySamples
{
    [Title("Entity SQL Samples")]
    [Prefix("EntitySQL")]
    class EntitySQLSamples : SampleHarness
    {
        NorthwindEFModel.NorthwindEntities context;

        public EntitySQLSamples()
        {
            context = new NorthwindEFModel.NorthwindEntities();

        }

        #region Restriction Operators
        [Category("Restriction Operators")]
        [Title("Where - Entity SQL Literal")]
        [Description("This sample finds all customers in Seattle.")]
        public void EntitySQLR1()
        {
            string entitySQL = "SELECT VALUE c FROM Customers AS c WHERE c.Address.City = 'Seattle';";
            ObjectQuery<Customer> query = context.CreateQuery<Customer>(entitySQL);

            ObjectDumper.Write(query);
        }


        [Category("Restriction Operators")]
        [Title("Where - DateTime Parameter")]
        [Description("This sample find all orders placed during or after 1994.")]
        public void EntitySQLR2()
        {
            DateTime dt = new DateTime(1994, 1, 1);

            string entitySQL = "SELECT VALUE o FROM Orders AS o WHERE o.OrderDate > @orderDate;";
            ObjectParameter[] queryParams = { new ObjectParameter("orderDate", dt) };
            ObjectQuery<Order> query = context.CreateQuery<Order>(entitySQL, queryParams);

            // show top level objects only
            ObjectDumper.Write(query, 1);
        }

        [Category("Restriction Operators")]
        [Title("Where - Composite predicate")]
        [Description("This sample find Products that have stock below their reorder level and have a units on order of zero.")]
        public void EntitySQLR3()
        {
            string entitySQL = "SELECT VALUE p FROM Products AS p WHERE p.UnitsInStock < p.ReorderLevel AND p.UnitsOnOrder = 0;";
            ObjectQuery<Product> query = context.CreateQuery<Product>(entitySQL);

            // show top level objects only
            ObjectDumper.Write(query, 1);
        }


        [Category("Restriction Operators")]
        [Title("Where - Decimal Parameter")]
        [Description("This sample finds Products that have a UnitPrice less than 10.")]
        public void EntitySQLR4()
        {
            decimal price = 10;

            string entitySQL = "SELECT VALUE p FROM Products AS p WHERE p.UnitPrice < @price;";
            ObjectParameter[] queryParams = { new ObjectParameter("price", price) };
            ObjectQuery<Product> query = context.CreateQuery<Product>(entitySQL, queryParams);

            // show top level objects only
            ObjectDumper.Write(query, 1);
        }

        [Category("Restriction Operators")]
        [Title("Where - Related Entities 1")]
        [Description("This sample finds Orders for Customers in Mexico.")]
        public void EntitySQLR5()
        {
            string entitySQL = "SELECT VALUE o FROM Orders AS o WHERE o.Customer.Address.Country = 'Mexico';";
            ObjectQuery<Order> query = context.CreateQuery<Order>(entitySQL);

            // show top level objects only
            ObjectDumper.Write(query, 1);
        }


        [Category("Restriction Operators")]
        [Title("Where - Related Entities 2")]
        [Description("This sample finds orders for customers not in Mexico or the UK.")]
        public void EntitySQLR7()
        {
            string entitySQL = "SELECT VALUE o FROM Orders AS o WHERE o.Customer.Address.Country <> 'UK' AND o.Customer.Address.Country <> 'Mexico'  ;";
            ObjectQuery<Order> query = context.CreateQuery<Order>(entitySQL);

            // show top level objects only
            ObjectDumper.Write(query, 1);

        }

        [Category("Restriction Operators")]
        [Title("Exists")]
        [Description("This sample finds suppliers for any out-of-stock products.")]
        public void EntitySQLR8()
        {
            string entitySQL = "SELECT VALUE s FROM Suppliers AS s WHERE EXISTS(SELECT p FROM s.Products as p WHERE p.UnitsInStock = 0);";
            ObjectQuery<Supplier> query = context.CreateQuery<Supplier>(entitySQL);

            // show top level objects only
            ObjectDumper.Write(query, 1);
        }

        #endregion

        #region Aggregate Operators

        [Category("Aggregate Operators")]
        [Title("SqlServer Count - Simple")]
        [Description("This sample uses COUNT to get the number of Orders.")]
        public void EntitySQLA1()
        {
            string entitySQL = "SELECT VALUE SqlServer.Count(o.OrderID) FROM Orders AS o;";
            ObjectQuery<Int32> query = context.CreateQuery<Int32>(entitySQL);

            ObjectDumper.Write(query, 1);
        }


        [Category("Aggregate Operators")]
        [Title("Count - Predicate 1")]
        [Description("This sample uses COUNT to get the number of Orders placed by Customers in Mexico.")]
        public void EntitySQLA2()
        {
            string entitySQL = "SELECT VALUE SqlServer.Count(o.OrderID) FROM Orders AS o WHERE o.Customer.Address.Country = 'Mexico';";
            ObjectQuery<Int32> query = context.CreateQuery<Int32>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Count - Predicate 2")]
        [Description("This sample uses COUNT to get the number of Orders shipped to Mexico.")]
        public void EntitySQLA3()
        {
            string entitySQL = "SELECT VALUE SqlServer.Count(o.OrderID) FROM Orders AS o WHERE o.ShipCountry == 'Mexico';";
            ObjectQuery<Int32> query = context.CreateQuery<Int32>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Sum - Simple 1")]
        [Description("This sample uses SUM to find the total freight over all Orders.")]
        public void EntitySQLA4()
        {
            string entitySQL = "SELECT VALUE SqlServer.Sum(o.Freight) FROM Orders AS o;";
            ObjectQuery<Decimal> query = context.CreateQuery<Decimal>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Sum - Simple 2")]
        [Description("This sample uses SUM to find the total number of units on order over all Products.")]
        public void EntitySQLA5()
        {
            string entitySQL = "SELECT VALUE SqlServer.Sum(p.UnitsOnOrder) FROM Products AS p;";
            ObjectQuery<Int32> query = context.CreateQuery<Int32>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Sum - Simple 3")]
        [Description("This sample uses SUM to find the total number of units on order over all Products out-of-stock.")]
        public void EntitySQLA6()
        {
            string entitySQL = "SELECT VALUE SqlServer.Sum(p.UnitsOnOrder) FROM Products AS p WHERE p.UnitsInStock = 0;";
            ObjectQuery<Int32> query = context.CreateQuery<Int32>(entitySQL);

            ObjectDumper.Write(query, 1);
        }


        [Category("Aggregate Operators")]
        [Title("Min - Simple 1")]
        [Description("This sample uses MIN to find the lowest unit price of any Product.")]
        public void EntitySQLA7()
        {
            string entitySQL = "SELECT VALUE SqlServer.Min(p.UnitPrice) FROM Products AS p;";
            ObjectQuery<Decimal> query = context.CreateQuery<Decimal>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Min - Simple 2")]
        [Description("This sample uses MIN to find the lowest freight of any Order.")]
        public void EntitySQLA8()
        {
            string entitySQL = "SELECT VALUE SqlServer.Sum(o.Freight) FROM Orders AS o;";
            ObjectQuery<Decimal> query = context.CreateQuery<Decimal>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Min - Predicate")]
        [Description("This sample uses MIN to find the lowest freight of any Order shipped to Mexico.")]
        public void EntitySQLA9()
        {
            string entitySQL = "SELECT VALUE SqlServer.Sum(o.Freight) FROM Orders AS o WHERE o.ShipCountry = 'Mexico';";
            ObjectQuery<Decimal> query = context.CreateQuery<Decimal>(entitySQL);

            ObjectDumper.Write(query, 1);
        }


        [Category("Aggregate Operators")]
        [Title("Min - Grouping")]
        [Description("This sample uses Min to find the Products that have the lowest unit price in each category.")]
        public void EntitySQLA10()
        {
            string entitySQL = "SELECT SqlServer.Min(p.UnitPrice) as MinPrice, p.Category.CategoryName FROM Products AS p GROUP BY p.Category.CategoryName;";
            ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Max - Simple 1")]
        [Description("This sample uses MAX to find the latest hire date of any Employee.")]
        public void EntitySQLA11()
        {
            string entitySQL = "SELECT VALUE SqlServer.Max(e.HireDate) FROM Employees AS e;";
            ObjectQuery<DateTime> query = context.CreateQuery<DateTime>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Max - Simple 2")]
        [Description("This sample uses MAX to find the most units in stock of any Product.")]
        public void EntitySQLA12()
        {
            string entitySQL = "SELECT VALUE SqlServer.Max(p.UnitsInStock) FROM Products AS p;";
            ObjectQuery<Int16> query = context.CreateQuery<Int16>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Max - Predicate")]
        [Description("This sample uses MAX to find the most units in stock of any Product with CategoryID = 1.")]
        public void EntitySQLA13()
        {
            string entitySQL = "SELECT VALUE SqlServer.Max(p.UnitsInStock) FROM Products AS p WHERE p.Category.CategoryID = 1;";
            ObjectQuery<Int16> query = context.CreateQuery<Int16>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Max - Grouping")]
        [Description("This sample uses MAX to find the Products that have the highest unit price in each category, and returns the result as an anonoymous type.")]
        public void EntitySQLA14()
        {
            string entitySQL = "SELECT SqlServer.Max(p.UnitPrice), k FROM Products AS p GROUP BY p.Category.CategoryName AS k;";
            ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Average - Simple 1")]
        [Description("This sample uses AVERAGE to find the average freight of all Orders.")]
        public void EntitySQLA15()
        {
            string entitySQL = "SELECT VALUE SqlServer.Avg(o.Freight) FROM Orders AS o;";
            ObjectQuery<Decimal> query = context.CreateQuery<Decimal>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Average - Simple 2")]
        [Description("This sample uses AVERAGE to find the average unit price of all Products.")]
        public void EntitySQLA16()
        {
            string entitySQL = "SELECT VALUE SqlServer.Avg(p.UnitPrice) FROM Products AS p;";
            ObjectQuery<Decimal> query = context.CreateQuery<Decimal>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Average - Predicate")]
        [Description("This sample uses AVERAGE to find the average unit price of all Products with CategoryID = 1.")]
        public void EntitySQLA17()
        {
            string entitySQL = "SELECT VALUE SqlServer.Avg(p.UnitPrice) FROM Products AS p WHERE p.Category.CategoryID = 1;";
            ObjectQuery<Decimal> query = context.CreateQuery<Decimal>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Average - Grouping 2")]
        [Description("This sample uses AVERAGE to find the average unit price of each category.")]
        public void EntitySQLA19()
        {
            string entitySQL = "SELECT SqlServer.Avg(p.UnitPrice) as AvgPrice, p.Category.CategoryName FROM Products AS p GROUP BY p.Category.CategoryName;";
            ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(entitySQL);

            ObjectDumper.Write(query, 1);
        }
        

        #endregion       

        #region Ordering and Grouping

        [Category("Ordering and Grouping")]
        [Title("Order By - Simple 1")]
        [Description("Select all customers ordered by ContactName.")]
        public void EntitySQLO1()
        {
            string entitySQL = "SELECT VALUE c FROM Customers AS c ORDER BY c.ContactName;";
            ObjectQuery<Customer> query = context.CreateQuery<Customer>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Ordering and Grouping")]
        [Title("Order By - Simple 2")]
        [Description("Select all customers ordered by ContactName descending.")]
        public void EntitySQLO2()
        {
            string entitySQL = "SELECT VALUE c FROM Customers AS c ORDER BY c.ContactName DESC;";
            ObjectQuery<Customer> query = context.CreateQuery<Customer>(entitySQL);

            ObjectDumper.Write(query, 1);
        }


        [Category("Ordering and Grouping")]
        [Title("Order By - Related Entities")]
        [Description("")]
        public void EntitySQL57()
        {

            string entitySQL = "select value c2.c from (select c,  anyelement(select SqlServer.Count(o.OrderId) from c.Orders as o) as orderCount from Customers as c) as c2 order by c2.orderCount";

            //string entitySQL = "SELECT VALUE c FROM Customers AS c ORDER BY SqlServer.Count(c.Orders.OrderID);";
            ObjectQuery<Customer> query = context.CreateQuery<Customer>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Ordering and Grouping")]
        [Title("Order By - Related Entities with Predicate")]
        [Description("")]
        public void EntitySQL58()
        {
            //var query = context.Customers.Select(c => c).OrderByDescending(c2 => c2.Region);

            //ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("Order By - Multiple Ordering ")]
        [Description("")]
        public void EntitySQL61()
        {
            //var query = context.Customers.Select(c => c).OrderBy(c => c.Region).ThenByDescending(c => c.ContactName);

            //ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("Order By - FK Collection")]
        [Description("Select all orders for a customer ordered by date that the order was placed.")]
        public void EntitySQL63()
        {
            //var query = context.Customers.Where(cust => cust.CustomerID == "ALFKI")
            //    .SelectMany(c => c.Orders.Select(o => o))
            //    .OrderBy(o2 => o2.OrderDate);

            //foreach (var order in query)
            //{
            //    ObjectDumper.Write(order);
            //}
        }

        [Category("Ordering and Grouping")]
        [Title("Grouping - Simple 1")]
        [Description("Select all Regions with a customer.")]
        public void EntitySQL64()
        {
            //var query = from c in context.Customers
            //            group c by c.Region into regions
            //            select new { regions.Key };

            //ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("Grouping - Simple 2")]
        [Description("Select all dates with orders placed.")]
        public void EntitySQL65()
        {
            //var query = from o in context.Orders
            //            group o by o.OrderDate into dates
            //            select new { dates.Key };

            //ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("Grouping - Join 1")]
        [Description("Select all Regions and customer count for each region.")]
        public void EntitySQL66()
        {
            //var query = from c in context.Customers
            //            group c by c.Region into regions
            //            select new { region = regions.Key, count = regions.Count() };

            //ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("Grouping - Join 2")]
        [Description("Select all Regions and customer count for each region using LINQ operator.")]
        public void EntitySQL67()
        {
            //var query = context.Customers.GroupBy(c => c.Region).Select(r => new { region = r.Key, count = r.Count() });

            //ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("Grouping with a join on Key 1")]
        [Description("Select all Customer Regions with the total Freight on all orders for Customers in that Region.")]
        public void EntitySQL68()
        {
            //var query = from c in context.Customers
            //            group c by c.Region into regions
            //            join c2 in context.Customers on regions.Key equals c2.Region
            //            select new { region = regions.Key, total = c2.Orders.Sum(o => o.Freight) };

            //ObjectDumper.Write(query);
        }

        [Category("Ordering and Grouping")]
        [Title("Grouping with a join on Key 2")]
        [Description("Select all Customer Regions with the total Freight on all orders for Customers in that Region using LINQ operators.")]
        public void EntitySQL69()
        {
            //var query = context.Customers.GroupBy(c => c.Region)
            //    .Select(g => new
            //                 {
            //                     Region = g.Key,
            //                     FreightTotal = g
            //                                      .SelectMany(c2 => c2.Orders)
            //                                      .Sum(o => o.Freight)
            //                 });

            //ObjectDumper.Write(query);
        }


        #endregion
        
        #region Inheritance

        //[Category("Table per Hierarchy Inheritance")]
        //[Title("One Level Hierarchy - Simple")]
        //[Description("Select all products, both active and discontinued products, and shows the type.")]
        //public void EntitySQL77()
        //{
        //    var query = context
        //        .Products
        //        .Select(p => p);

        //    // we need ToList to force execution, as GetType is not defined in store
        //    var query2 = query
        //        .ToList()
        //        .Select(p => new { type = p.GetType().ToString(), prod = p });

        //    ObjectDumper.Write(query2);
        //}


        //[Category("Table per Hierarchy Inheritance")]
        //[Title("One Level Hierarchy - OfType - Simple 1")]
        //[Description("Select only discontinued products.")]
        //public void EntitySQL78()
        //{
        //    var query = context.Products.OfType<DiscontinuedProduct>().Select(p => p);

        //    ObjectDumper.Write(query);
        //}


        //[Category("Table per Hierarchy Inheritance")]
        //[Title("One Level Hierarchy - OfType - Simple 2")]
        //[Description("Select only products, which will reutrn all Products and subtypes of Products (DiscontinuedProducts and ActiveProducts).")]
        //public void EntitySQL79()
        //{
        //    var query = context.Products.OfType<Product>().Select(p => p);

        //    ObjectDumper.Write(query);
        //}

        //[Category("Table per Hierarchy Inheritance")]
        //[Title("One Level Hierarchy - Getting Supertype - OfType")]
        //[Description("Select only active products.")]
        //public void EntitySQL80()
        //{
        //    var query = context.Products.OfType<ActiveProduct>();

        //    ObjectDumper.Write(query);
        //}

        //[Category("Table per Hierarchy Inheritance")]
        //[Title("One Level Hierarchy - Getting Supertype - Local")]
        //[Description("Select only discontinued products.")]
        //public void EntitySQL81()
        //{
        //    // get a local copy, as 'is' not defined on server
        //    var q1 = context.Products.ToList();

        //    var query = q1.Where(p => p is DiscontinuedProduct);

        //    ObjectDumper.Write(query);
        //}


        //[Category("Table per Hierarchy Inheritance")]
        //[Title("Complex Hierarchy - Simple")]
        //[Description("Select all contacts and show the type of each.")]
        //public void EntitySQL82()
        //{
        //    var query = context
        //        .Contacts
        //        .Select(c => c);

        //    // we need ToList to force execution, as GetType is not defined in store
        //    var query2 = query
        //        .ToList()
        //        .Select(c => new { type = c.GetType().ToString() });

        //    ObjectDumper.Write(query2);
        //}

        //[Category("Table per Hierarchy Inheritance")]
        //[Title("Complex Hierarchy - OfType 1")]
        //[Description("Select all Shipper contacts.")]
        //public void EntitySQL83()
        //{
        //    var query = context
        //        .Contacts
        //        .OfType<ShipperContact>()
        //        .Select(c => c);

        //    ObjectDumper.Write(query);
        //}

        //[Category("Table per Hierarchy Inheritance")]
        //[Title("Complex Hierarchy - OfType 2")]
        //[Description("Select all Full contacts, which includes suppliers, customers, and employees.")]
        //public void EntitySQL84()
        //{
        //    var query = context
        //        .Contacts
        //        .OfType<FullContact>()
        //        .Select(c => c);

        //    ObjectDumper.Write(query);
        //}

        /* not enabled for Feb CTP
        [Category("Table per Hierarchy Inheritance")]
        [Title("Complex Hierarchy - using supertype")]
        [Description("Select all Customers and Employees, cast as FullContacts to allow join.")]
        public void EntitySQL85()
        {
            var query = context
                .Contacts
                .OfType<CustomerContact>()
                .Cast<FullContact>()
                .Union(context.Contacts.OfType<EmployeeContact>().Cast<FullContact>().Select(ec => ec ))
                .ToList()
                .Select(c => new {type = c.GetType().ToString(), companyName =  c.CompanyName } );

            ObjectDumper.Write(query);
        }

        [Category("Table per Concrete Type Inheritance")]
        [Title("Simple")]
        [Description("Select all federated products and display thier types.")]
        public void EntitySQL86()
        {
            var query = context.ProductsFedarated.ToList().Select(p => new { type = p.GetType().ToString(), p });

            ObjectDumper.Write(query);
        }

        [Category("Table per Concrete Type Inheritance")]
        [Title("OfType")]
        [Description("Select all discontinued federated products.")]
        public void EntitySQL87()
        {
            var query = context.ProductsFedarated.OfType<DiscontinuedProductFedarated>().ToList().Select(p => new { type = p.GetType().ToString(), p });

            ObjectDumper.Write(query);
        }

        [Category("Table per Type Inheritance")]
        [Title("Simple")]
        [Description("Select all contacts and shows their types.")]
        public void EntitySQL88()
        {
            var query = context.ContactsSplit.ToList().Select(c => new { type = c.GetType().ToString(), c });

            ObjectDumper.Write(query);
        }

        [Category("Table per Type Inheritance")]
        [Title("OfType 1")]
        [Description("Select all Customers.")]
        public void EntitySQL89()
        {
            var query = context
                .ContactsSplit
                .OfType<CustomerContactSplit>()
                .ToList()
                .Select(c => new { type = c.GetType().ToString(), c });

            ObjectDumper.Write(query);
        }

        [Category("Table per Type Inheritance")]
        [Title("OfType 2")]
        [Description("Select all Customers who are also employees, both as the base ContactSplit type (empty set).")]
        public void EntitySQL90()
        {
            var query = context
                .ContactsSplit
                .OfType<CustomerContactSplit>()
                .Cast<ContactSplit>()
                .Intersect(context.ContactsSplit.OfType<EmployeeContactSplit>().Cast<ContactSplit>())
                .ToList()
                .Select(c => new { type = c.GetType().ToString(), c });

            ObjectDumper.Write(query);
        }

        
        */
        #endregion


    }
}

